<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@page import="support.*,java.util.*,java.sql.*,javax.sql.*"%>
<%@ taglib uri="http://struts.apache.org/tags-html" prefix="html"%>
<%@ taglib uri="http://struts.apache.org/tags-bean" prefix="bean"%>
<%@taglib uri="/WEB-INF/struts-logic.tld" prefix="logic"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Confirmation Page</title>
</head>
<body>
	<p>
		<%
			String first = session.getAttribute("FirstName").toString();
			String middle = session.getAttribute("MiddleI").toString();
			String last = session.getAttribute("LastName").toString();
			String citizenship = session.getAttribute("CountryOfCitizenship")
					.toString();
			String resident = session.getAttribute("CountryOfResident")
					.toString();
			String USResident = session.getAttribute("USResident").toString();
			String street = session.getAttribute("StreetAddress").toString();
			String city = session.getAttribute("City").toString();
			String state = session.getAttribute("AddressState").toString();
			String zip = session.getAttribute("ZipCode").toString();
			String area = session.getAttribute("AreaCode").toString();
			String number = session.getAttribute("Number").toString();
			String specialization = session.getAttribute("specailization")
					.toString();
			int appid = 0;
		%>
		Hello,
		<%=session.getAttribute("FirstName")%>
		<%=session.getAttribute("MiddleI")%>
		<%=session.getAttribute("LastName")%>

		<%
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Connection conn = null;
			Statement stmt = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			String instruc = null;
			int addid = 0, nameid = 0, counid = 0, citizid = 0, resid = 0, degid = 0, schlid = 0, mainid = 0, specid = 0, majid = 0;

			try {
				// Registering Postgresql JDBC driver
				Class.forName("org.postgresql.Driver");
				// Open a connection to the database
				conn = DriverManager.getConnection(url, "postgres", "postgres");
				// Create the statement
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_UPDATABLE);

				// give a APP ID to application, Starting 1000.
				appid = 0;
				rs = stmt.executeQuery("SELECT AppID FROM MainTable");
				while (rs.next()) {
					appid = rs.getInt("AppID");
				}
				appid++;

				//a.setAppID(appid);

				// store Names info into database
				instruc = "INSERT INTO NAME(NameID, First, Middle, Last)"
						+ "VALUES(?,?,?,?)";

				// find and last id # and +1
				nameid = 0;
				rs = stmt.executeQuery("SELECT NameID FROM NAME");
				while (rs.next()) {
					nameid = rs.getInt("NameID");
				}
				nameid++;

				ps = conn.prepareStatement(instruc);
				ps.setInt(1, nameid);
				ps.setString(2, first);
				ps.setString(3, middle);
				ps.setString(4, last);
				ps.executeUpdate();

				// store Address info into database

				instruc = "INSERT INTO ADDRESS(AddID, Street, City, State,"
						+ "Zip, AreaCode, Phone) VALUES(?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(instruc);

				// find and last id # and +1
				addid = 0;
				rs = stmt.executeQuery("SELECT AddID FROM ADDRESS");
				while (rs.next()) {
					addid = rs.getInt("AddID");
				}
				addid++;

				ps.setInt(1, addid);
				ps.setString(2, street);
				ps.setString(3, city);
				ps.setString(4, state);
				ps.setString(5, zip);
				ps.setString(6, area);
				ps.setString(7, number);
				ps.executeUpdate();

				// store citizenship, residence, residency info into database
				instruc = "INSERT INTO Country(CounID, Citizenship, Residence,"
						+ " Residency) VALUES(?,?,?,?)";
				ps = conn.prepareStatement(instruc);

				// find and last id # and +1
				counid = 0;
				rs = stmt.executeQuery("SELECT CounID FROM Country");
				while (rs.next()) {
					counid = rs.getInt("CounID");
				}
				counid++;

				citizid = 0;
				rs = stmt
						.executeQuery("SELECT ID FROM Countries WHERE Country = '"
								+ citizenship + "'");
				while (rs.next()) {
					citizid = rs.getInt("ID");
				}

				resid = 0;
				rs = stmt
						.executeQuery("SELECT ID FROM Countries WHERE Country = '"
								+ resident + "'");
				while (rs.next()) {
					resid = rs.getInt("ID");
				}

				ps.setInt(1, counid);
				ps.setInt(2, citizid);
				ps.setInt(3, resid);
				ps.setString(4, USResident);
				ps.executeUpdate();

				int apptableid = 0;
				rs = stmt.executeQuery("SELECT app_id FROM Application");
				while (rs.next()) {
					if (rs.getInt("app_id") > apptableid) {
						apptableid = rs.getInt("app_id");
					}
				}
				apptableid++;

				// store application relate info into database
				instruc = "INSERT INTO Application(app_id,AppID,"
						+ " numRev, status, Grade1,"
						+ " Grade2, Grade3, ready) VALUES(?,?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(instruc);
				ps.setInt(1, apptableid);
				ps.setInt(2, appid);
				ps.setInt(3, 0);
				ps.setString(4, "Pending");
				ps.setInt(5, 0);
				ps.setInt(6, 0);
				ps.setInt(7, 0);
				ps.setString(8, "NO");
				ps.executeUpdate();

				// store degree info into database
				int discpid = 0, univid = 0, addNew = 0, numDisc = 0, numSpec = 0;
				String disc = null, spec = null;
				rs = stmt.executeQuery("SELECT * FROM Disciplines");
				while (rs.next()) {
					if (rs.getInt("ID") > discpid) {
						discpid = rs.getInt("ID");
					}

					// add only new discipline to the list of disciplines,
					// no duplicates!
					if (session.getAttribute("degreeDiscipline").toString()
							.equals(rs.getString("Discipline"))) {
						addNew = 0;
						break;
					} else {
						addNew = 1;
					}
				}
				discpid++;

				if (addNew == 1) {
					instruc = "INSERT INTO Disciplines(ID,Discipline) VALUES(?,?)";
					ps = conn.prepareStatement(instruc);
					ps.setInt(1, discpid);
					ps.setString(2, session.getAttribute("degreeDiscipline")
							.toString());
					ps.executeUpdate();
				}

				String loc = session.getAttribute("location").toString();
				String univ = session.getAttribute("university").toString();
				rs = stmt.executeQuery("SELECT * FROM Universities");
				while (rs.next()) {
					if (rs.getInt("ID") > univid) {
						univid = rs.getInt("ID");
					}

					if (univ.equals(rs.getString("University"))) {
						addNew = 0;
						break;
					} else {
						addNew = 1;
					}
				}
				univid++;
				if (addNew == 1) {
					instruc = "INSERT INTO Universities(ID,Location,University) VALUES(?,?,?)";
					ps = conn.prepareStatement(instruc);
					ps.setInt(1, univid);
					ps.setString(2, loc);
					ps.setString(3, univ);
					ps.executeUpdate();
				}

				instruc = "INSERT INTO Degree(DegID,School,Major,Month,Year,"
						+ "Title,GPA) VALUES(?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(instruc);

				// find the last id # and +1
				degid = 0;
				rs = stmt.executeQuery("SELECT DegID FROM Degree");
				while (rs.next()) {
					if (rs.getInt("DegID") > degid) {
						degid = rs.getInt("DegID");
					}
				}
				degid++;

				schlid = 0;
				rs = stmt
						.executeQuery("SELECT ID FROM Universities WHERE Location = '"
								+ loc + "' AND University = '" + univ + "'");
				while (rs.next()) {
					schlid = rs.getInt("ID");
				}

				String maj = session.getAttribute("degreeDiscipline")
						.toString();

				majid = 0;
				rs = stmt
						.executeQuery("SELECT ID FROM Disciplines WHERE Discipline =  '"
								+ maj + "'");
				while (rs.next()) {
					majid = rs.getInt("ID");
				}

				ps.setInt(1, degid);
				ps.setInt(2, schlid);
				ps.setInt(3, majid);
				ps.setString(4, session.getAttribute("month").toString());
				ps.setString(5, session.getAttribute("year").toString());
				ps.setString(6, session.getAttribute("GPA").toString());
				ps.setString(7, session.getAttribute("Title").toString());
				ps.executeUpdate();

				specid = 0;
				rs = stmt
						.executeQuery("SELECT ID FROM Specializations WHERE Specialization = '"
								+ specialization + "'");
				while (rs.next()) {
					specid = rs.getInt("ID");
				}

				// find and last id # and +1 for main table
				mainid = 0;
				rs = stmt.executeQuery("SELECT TableID FROM MainTable");
				while (rs.next()) {
					mainid = rs.getInt("TableID");
				}
				mainid++;
				instruc = "INSERT INTO MainTable(TableID, AppID, NAMEID, AddID,"
						+ "DegID, MajID, SpecID, CounID) VALUES(?,?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(instruc);
				ps.setInt(1, mainid);
				ps.setInt(2, apptableid);
				ps.setInt(3, nameid);
				ps.setInt(4, addid);
				ps.setInt(5, degid);
				ps.setInt(6, majid);
				ps.setInt(7, specid);
				ps.setInt(8, counid);
				ps.executeUpdate();

				// to handle when student has more than one degrees
				Vector<university> uni = (Vector<university>) session
						.getAttribute("uni");
				for (int i = 0; i < uni.size(); i++) {
					rs = stmt.executeQuery("SELECT * FROM Disciplines");
					while (rs.next()) {
						if (rs.getInt("ID") > discpid) {
							discpid = rs.getInt("ID");
						}

						// add only new discipline to the list of disciplines,
						// no duplicates!
						if (uni.get(i).getDegreeDiscipline()
								.equals(rs.getString("Discipline"))) {
							addNew = 0;
							break;
						} else {
							addNew = 1;
						}
					}
					discpid++;

					if (addNew == 1) {
						instruc = "INSERT INTO Disciplines(ID,Discipline) VALUES(?,?)";
						ps = conn.prepareStatement(instruc);
						ps.setInt(1, discpid);
						ps.setString(2, uni.get(i).getDegreeDiscipline());
						ps.executeUpdate();
					}

					loc = uni.get(i).getLocation();
					univ = uni.get(i).getUniversity();
					rs = stmt.executeQuery("SELECT * FROM Universities");
					while (rs.next()) {
						if (rs.getInt("ID") > univid) {
							univid = rs.getInt("ID");
						}

						if (univ.equals(rs.getString("University"))) {
							addNew = 0;
							break;
						} else {
							addNew = 1;
						}
					}
					univid++;
					if (addNew == 1) {
						instruc = "INSERT INTO Universities(ID,Location,University) VALUES(?,?,?)";
						ps = conn.prepareStatement(instruc);
						ps.setInt(1, univid);
						ps.setString(2, loc);
						ps.setString(3, univ);
						ps.executeUpdate();
					}

					instruc = "INSERT INTO Degree(DegID,School,Major,Month,Year,"
							+ "Title,GPA) VALUES(?,?,?,?,?,?,?)";
					ps = conn.prepareStatement(instruc);

					// find the last id # and +1
					degid = 0;
					rs = stmt.executeQuery("SELECT DegID FROM Degree");
					while (rs.next()) {
						if (rs.getInt("DegID") > degid) {
							degid = rs.getInt("DegID");
						}
					}
					degid++;

					schlid = 0;
					rs = stmt
							.executeQuery("SELECT ID FROM Universities WHERE Location = '"
									+ loc + "' AND University = '" + univ + "'");
					while (rs.next()) {
						schlid = rs.getInt("ID");
					}

					maj = uni.get(i).getDegreeDiscipline();

					majid = 0;
					rs = stmt
							.executeQuery("SELECT ID FROM Disciplines WHERE Discipline =  '"
									+ maj + "'");
					while (rs.next()) {
						majid = rs.getInt("ID");
					}

					ps.setInt(1, degid);
					ps.setInt(2, schlid);
					ps.setInt(3, majid);
					ps.setString(4, uni.get(i).getMonth());
					ps.setString(5, uni.get(i).getYear());
					ps.setString(6, uni.get(i).getTitle());
					ps.setString(7, uni.get(i).getGPA());
					ps.executeUpdate();

					specid = 0;
					rs = stmt
							.executeQuery("SELECT ID FROM Specializations WHERE Specialization = '"
									+ specialization + "'");
					while (rs.next()) {
						specid = rs.getInt("ID");
					}

					// find and last id # and +1 for main table
					mainid = 0;
					rs = stmt.executeQuery("SELECT TableID FROM MainTable");
					while (rs.next()) {
						mainid = rs.getInt("TableID");
					}
					mainid++;
					instruc = "INSERT INTO MainTable(TableID, AppID, NAMEID, AddID,"
							+ "DegID, MajID, SpecID, CounID) VALUES(?,?,?,?,?,?,?,?)";
					ps = conn.prepareStatement(instruc);
					ps.setInt(1, mainid);
					ps.setInt(2, apptableid);
					ps.setInt(3, nameid);
					ps.setInt(4, addid);
					ps.setInt(5, degid);
					ps.setInt(6, majid);
					ps.setInt(7, specid);
					ps.setInt(8, counid);
					ps.executeUpdate();
				}// forloop

				instruc = "UPDATE users SET APP = '" + nameid
						+ "' WHERE user_name ='"
						+ request.getUserPrincipal().getName() + "'";
				ps = conn.prepareStatement(instruc);
				ps.executeUpdate();

			} catch (SQLException e) {
				System.out.println(e);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			// close connection
			try {
				rs.close();
				stmt.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		%>
	
	<p>
		Thanks for your application. Your application ID is:
		<%=appid%>
		<%
			java.text.DateFormat df = new java.text.SimpleDateFormat(
					"dd/MM/yyyy");
		%>
	
	<p>
		Current Date:
		<%=df.format(new java.util.Date())%>

	<p>
		<html:form action="/Logoff">
			<html:submit value="Logoff" />
		</html:form></body>
</html>